Database Management

Information concerning the management of DEACOM databases

Building a Test Environment

Test environments are useful for testing major updates and helpful for training new employees in a safe environment, risk free of potentially impacting your Production environment. DEACOM offers the Test System Builder utility which automates the process of creating new databases and restoring current data to the test environment. 

Configuration

Prior to creating a test environment, the following setup steps are required:

  • A backup of the DEACOM Production databases must be made via SQL Server Management Studio. See the Database Backup help page for additional information.
  • A new folder, entitled "Test" must be created on your DEACOM Application Server.

Process

The best way to create a new Test Environment is to use the Test System Builder.

  1. Navigate to the root DEACOM directory and select the testsystembuilder.exe executable file. If possible, run the TestSystemBuilder on the machine that houses the SQL Server so that the proper permissions are in place.
  2. Input the information prompted for as shown below and click the "Save" button.
    • Test System Folder - Set this to be the location where you will be installing your test environment. You may either select the test folder to overwrite, or create a new folder in which to install the new environment. Be sure to specify an exact folder rather than an entire drive as the Test System Builder automatically overwrites anything that is contained within this directory. Using this function you can overwrite previous test environments with your newest data.
    • Test Database Folder - Display only. Indicates the current defaults for SQL Server Management Studio for both mdf and ldf files.
    • Server - Select the server where the test databases will be created and restored to within SQL.
    • Test Service Port - Defaults to 92.
    • Test Web Port - Default to 8081. If no value is entered, goes to production defaults.
    • Test Database Name - Here users have the ability to specifically name the test databases before proceeding with the creation of the test environment. This function automatically creates a new database in SQL based off of your current database naming scheme. This field is automatically populated in the Test System Builder with “Test”. For example, if the current naming scheme is Deacom, DeacomSystem, and DeacomDocs the new test databases will be named TestDeacom, TestDeacomSystem, and TestDeacomDocs unless otherwise specified. Notes: 1.) This field may not be left blank. 2.) Database names may contain numbers, but a number cannot be the first character.
    • Test Company Name - Similar to the Test Database Name, users have the ability to enter a new company name for the test environment. The Test System Builder automatically populates with **TEST**. Users may change the default name. This field may not be left blank.
  3. Once the test system has successfully been created, users should check the .ini file to ensure the paths contained in that file are correct. Additional information on the use and setup of the .ini file is available via DEACOM Ini File.

FAQ & Diagnostic Tips

Tip: When using the Test System Builder, when selecting the “Test Database Folder”, make sure that you do NOT select your Production folder or else you will overwrite your production environment!

Building a Multi-Company DEACOM Environment

Multi-company environments are useful for companies that operate under different names and contain sub companies. This is also useful for facilities that have very different functionalities that are best kept separate. DEACOM gives customers the ability to manage multiple companies with different Vendors, Customers, products, and production methods through the creation of multiple company databases.

Configuration

Prior to creating a multi-company DEACOM environment, the following setup is required.

  • A backup of the DEACOM Production databases must be made via SQL Server Management Studio. See the Database Backup help page for additional information.
  • Master data consideration and requirements for the new company should be prepared. A listing of the minimal data setups is available via the Company Databases page.

Security

By default, all users have access to log into the new company that was created. This can be changed in System > Maintenance > User Restrictions where "Access to the Company" can be changed from Yes to No. Individual user security settings will be the same in all companies that the user has access to. User restrictions on the other hand can be set up based on which company a user is logged into.

Additionally, there is security to limit who can create or maintain Company Databases. Security access to "System -- Maintenance" must be assigned for a user to access Company Databases. For more information on security to limit access, please see System > Maintenance > User Group Master Security.

After individual users have been set up with the correct company database access, the setup and maintenance of the new company is complete.

Process

Creating a new company database

When a new company (company database) is created, all tables within the new database will be empty. Tables may be manually entered, or populated through an Import Data transaction at Tools > Import Data. Before attempting an Import Data transaction, please read the warning below this section. More complex data files and uploads may be managed by Deacom in the form of data consultation. The steps to create a new company database are indicated below.

  1. Navigate to System > Maintenance > Company Databases.
  2. Click the "New" button to display the Edit Company Database form.
  3. Fill in all appropriate information, including required fields, which are marked in blue. The list below details all the fields and options on the Edit Company Database form.
    • Name: (New Company Name).
    • Description: (Company Name and info).
    • Document DB: (CompanyNameDocs).
    • Active: (check for active, check to make the company inactive).
    • Default: (checking this makes this the default option for logging in when opening DEACOM).
  4. Once all the appropriate information has been entered, click the "Save" and "Exit" buttons to complete the setup process.
  5. Once saved, the system will begin the process of creating the new company and documents database. The process takes between 2 - 5 minutes. Once finished, a message will display indicating that the company database has been successfully created. Click "OK" and close DEACOM completely before logging into the new company. The database will now be visible in the “Company” pick list box on the DEACOM login screen. Log in with the same username and password.

Data Import Warning

  • Importing data into DEACOM from any DEACOM-defined format is not recommended. Before performing any import, fully test the import in the company's training environment with a current copy of the data. Production databases must be backed up before importing data.
  • Calculations for imported data will not fire and all checks, including defaults, will be bypassed. To reapply calculations and checks, each piece of data must be re-saved individually after import in the new location.
  • Any technical support assistance to fix failed data imports is considered a billable project.

Setting up a new database (minimum requirements)

  • Accounting > Maintenance
    • Account Structure
    • Chart of Accounts Groups
    • Chart of Accounts
    • Fiscal Years
    • Fiscal Year Groups
    • Currencies
    • Terms
  • Accounting > Options
    • General tab - "Supervisor NPB" date
    • General tab - "No Posting Before" date
    • General tab - "No Posting After" date
    • Costing tab - "Inventory Costing" type
    • Costing tab - "WIP Materials" account
    • Accounts Payable tab - "Cash Payments" account
    • Accounts Payable tab - "A/P" account
    • Accounts Payable tab - "Purchase Discount" account
    • Accounts Receivable tab - "Cash Receipts" account
    • Accounts Receivable tab - "A/R" account
    • Accounts Receivable tab - "Sales Discount" account
  • System > Maintenance
    • Chart Group Security
  • Inventory > Maintenance
    • Item Search 1-5
    • Adjustment Reasons
  • Purchasing > Maintenance
    • Ship to Locations
  • Sales > Maintenance
    • Sales User 1-5
    • Sales Groups
  • CRM > Maintenance
    • Contact Search 1-5

Modifying an existing company database

Existing company databases should only be modified to change the "Description" field which can be referenced in reports. The "Name" and "DocumentDB" fields are the actual SQL database names. If they are changed without changing the SQL databases, users will no longer be able to log into DEACOM.

FAQ & Diagnostic Tips

Can the same inventory exist across multiple companies?

Each company database is unique with unique sets of data for inventory, sales orders, purchase orders, facilities, vendors, etc.

Tip: Common Data and associated definitions are as follows:

  • Company or Company Database - In DEACOM, a separate entity with its own database and supporting tables. Transactions posted in one Company are not visible in other Companies.
  • System or System Database - In DEACOM, the database that stores user information and settings, DEACOM version information, and the pointers to all associated company databases. Each system database may have multiple company databases assigned. Company databases may only have one associated system database. Users may be assigned/restricted to the appropriate company databases as necessary.
  • Document Database - In DEACOM, the database that will store all documents created via the “View Docs” button/feature found throughout DEACOM. This is new to versions 14.1 and higher. When updating from previous version of DEACOM users will be prompted to confirm/name the document database during the install process.
  • Facility - A separate division, plant or department within a company (company database). User access, transactions, G/L postings, and financial reporting can all be done on a facility basis. Users restricted to a specific facility or facility group will only be able to post to the appropriate facility specific G/L accounts*. In addition, all reports and pre filters in the system will be set to only display information relevant to the user’s facility access. Posting to facility specific G/L accounts requires the associated facility overrides and accounts to be set up and maintained.
  • User and User Group - Records that store login information and control system/transnational access.
  • User Restrictions - Settings that allow an individual user to be restricted by the following areas/groupings in DEACOM: Facility, Facility Group, Salesmen, Sales Group, Formulator, Item Planner, Labor Group, and Mobile Group. User restriction settings also control which company databases users may log into.

Tip: When viewing documents in View Docs*, a copy is put in your temp folder. If you have trouble opening the file, try to open the file directly from the temp folder and see if you have the correct program installed or if you are getting any messages.